import pymysql


class DbHandle:
    def __init__(self,host='localhost', user='root', password="123456",
                 database='quote', port=3306):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    def get_connect(self):  #连接数据库
        try:
            conn = pymysql.connect(host=self.host,user=self.user,password=self.password,
                               database=self.database,port=self.port)
            return conn
        except Exception as e:
            print('connect error!',e)

    def search_data(self,*args):  #查询数据
        try:
            self.conn = self.get_connect() #获取连接
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor) #创建游标
            if len(args) ==1:   #这种方式需要拼接

                self.cursor.execute(args[0]) #执行sql
            elif len(args)==2:  #这种方式直接传参
                self.cursor.execute(args[0],args[1])
            res = self.cursor.fetchall() #获取数据
            self.conn.commit() #提交
            return res #返回数据
        except Exception as e:
            self.conn.rollback()
            print('db operation error!',e)
        finally:
            self.cursor.close()
            self.conn.close()


    def update_data(self,sql):
        try:
            self.conn = self.get_connect()  # 获取连接
            self.cursor = self.conn.cursor()  # 创建游标
            self.cursor.execute(sql)  # 执行sql
            self.conn.commit() #提交事务
        except Exception as e:
            self.conn.rollback()  #回滚事务
            print(e,'db operation error!')
        finally:
            self.cursor.close()  #关闭游标
            self.conn.close()  #关闭连接




# if __name__ == '__main__':
#     a = DbHandle()
# #     a.update_data("update tb_user set grade='3' where username='tim'")
#     print(a.search_data("select productNO,otherInfo,price from tb_product where productNO=%s",['100']))